<?php
/**
 * 商户后台导出下载数据
 * User: 13651
 * Time: 2025/9/8 15:24
 */

namespace App\Jobs;

use App\Common\ResponseCode;
use App\Exceptions\BusinessException;
use App\Extensions\oss\Oss;
use App\Models\Bi\BiOrder;
use App\Models\Order;
use App\Models\OrderGoodsDetail;
use App\Models\StoreAllPayWayList;
use App\Repository\OrderExportRepo;
use App\Util\ExeclUtil;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;
use ZipArchive;

class OrderQueryExportJob implements ShouldQueue
{

    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    /**
     * 查询表别名
     */
    const TmpTableName = "tmp_orders";

    /**
     * 查询条件
     * @var array
     */
    protected $queryData;

    /**
     * 设置超时时间
     * @var int
     */
    public $timeout = 0;

    /**
     * 申请导出记录ID
     * @var int
     */
    protected $id;

    /**
     * 查询条件
     * @param array $queryParams
     */
    public function __construct(array $queryParams,int $id)
    {
        $this->queryData = $queryParams;
        $this->id = $id;
    }

    /**
     * 异步任务执行
     * @return void
     * @throws \Exception
     */
    public function handle()
    {
        ini_set('memory_limit', '500M');
        set_time_limit(0);
        Log::info('测试重启');
        /**
         * 查询条件
         */
        $where = $this->queryData;

        /**
         * 定义查询table
         */
        if (empty($where["start_date"]) || $where["start_date"] >= date("Y-m-d", time()) . " 00:00:00") {
            $prefix = "";
            $query = DB::connection();
            $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
            $query = $query->table("orders as " . self::TmpTableName);
        } else {
            $prefix = "bi_";
            if (!Schema::connection("bi")->hasTable($this->getTableName($where["start_date"], false))) {
                throw new \Exception("暂无数据");
            }
            $query = DB::connection("bi");
            $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
            $query = $query->table(DB::raw($this->getTableName($where["start_date"], true, $prefix) . " FORCE INDEX(created_at)"));
        }

        /**
         * 设置查询条件
         */
        $this->setWhere($query, $where, self::TmpTableName);

        $query->select([self::TmpTableName . ".*", "refund_orders.created_at as refund_time",
            "order_goods_details.coupon_amount as goods_coupon_amount", "order_goods_details.coupon_type as goods_coupon_type"]);
        $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");

        $goodsQuery = DB::table($prefix . "order_goods_details")->select([
            "out_trade_no", "coupon_type",
            DB::raw("sum(coupon_amount) as coupon_amount"),
        ])
            ->where("status", 1)
            ->groupBy(["out_trade_no", "coupon_type"]);
        $this->setWhere($goodsQuery, [
            "user_id" => $where["user_id"],
            "store_id" => $where["store_ids"],
            "start_date" => $where["start_date"],
            "end_date" => $where["end_date"]
        ]);
        $query->leftJoinSub($goodsQuery, $prefix . "order_goods_details", self::TmpTableName . ".out_trade_no", "=", "order_goods_details.out_trade_no");
        $orders = $query->orderBy(self::TmpTableName . ".created_at", "desc")->cursor();
        $company = StoreAllPayWayList::query()->get()->toArray();
        $company = array_combine(array_column($company, "company"), $company);

        /**
         * 定义表Tiele
         */
        $title = [
            '门店ID', '门店名称', "设备ID", '收银员', '订单号', "原订单号", '订单金额', '退款金额', "实收金额", '费率', '手续费', '支付方式', '支付状态', '付款时间', '通道', '退款时间', "退款类型", '备注', "买家ID", "优惠券类型", "优惠券金额"
        ];
        $fileName = "订单导出" . ($where["month"] ?? date("Y-m-d")) . "_" . time() . ".csv";
        $filePath = public_path("/uploads/csv/" . date("Y/md", time()) . "/");
        Log::info('商户订单导出文件保存地址'.$filePath.$fileName);

        /**
         * 写入excel
         */
        try {
            Log::info('商户订单导出文件开始');
            $this->flushFile($filePath, $fileName, $title, $orders, function ($order) use ($company) {
                $refund_type_name = "";
                if ($order->pay_status == 6) {
                    $refund_type_name = "当日退";
                    if (date("Ymd", strtotime((string)$order->created_at)) != date("Ymd", strtotime((string)$order->refund_time))) {
                        $refund_type_name = "隔日退";
                    }
                    if (date("Ym", strtotime((string)$order->created_at)) != date("Ym", strtotime((string)$order->refund_time))) {
                        $refund_type_name = "隔月退";
                    }
                }
                $coupon_type = 0;
                $coupon_type_text = "无";
                $coupon_amount = 0;

                if (!empty($order->goods_coupon_type) && $order->pay_status == 1) {
                    $coupon_type = $order->goods_coupon_type == "COUPON" ? 1 : 2;
                    $coupon_type_text = $order->goods_coupon_type == "COUPON" ? "预充" : "免充";
                    $coupon_amount = $order->goods_coupon_amount ?? 0;
                }
                if ($coupon_type == 0 && $order->discount_amount > 0 && $order->pay_status == 1) {
                    $coupon_type = 1;
                    $coupon_type_text = "预充";
                    $coupon_amount = $order->discount_amount;
                }
                return [
                    iconv('utf-8','gbk',$order->store_id."\t"),
                    $order->store_name,
                    iconv('utf-8','gbk',$order->device_id."\t"),
                    $order->merchant_name,
                    "'" . $order->out_trade_no,
                    iconv('utf-8','gbk',$order->trade_no."\t"),
                    $order->total_amount,
                    $order->refund_amount,
                    bcsub((string)$order->total_amount, (string)$order->refund_amount, 2),
                    $order->rate . "%",
                    $order->fee_amount,
                    $order->ways_type_desc,
                    $order->pay_status_desc,
                    $order->pay_time,
                    isset($company[$order->company]) ? $company[$order->company]["company_desc"] : "",
                    $order->refund_time,
                    $refund_type_name,
                    $order->remark,
                    $order->buyer_id,
                    $coupon_type_text,
                    $coupon_amount
                ];
            });

            $fileSize = filesize($filePath.$fileName);
            Log::info('商户订单导出文件大小'.$fileSize);

            /**
             * 对csv进行压缩
             */
            $zipArc = new ZipArchive();
            $zipName = $filePath.explode('.',$fileName)[0].'.zip';
            if ($zipArc->open($zipName, ZipArchive::CREATE) === false) {
                Log::info('商户订单导出文件打开csv失败');
                return;
            }
            $zipFilaPath = $filePath.$fileName;
            // 加密文件 此处文件名及路径是压缩包内的
            $zipFile = '导出数据/'.$fileName;
            $zipArc->addFile($zipFilaPath,$zipFile);
            $zipArc->close();
            Log::info("[商户订单导出文件压缩关闭]");

            /**
             * 上传至阿里云OSS
             */
            $result = Oss::getInstance()->uploadDoc(explode('.',$fileName)[0].'.zip',$zipName);
            if (!empty($result))
            {
                /**
                 * 更新数据表状态为已完成
                 */
                $log = [
                    'file_name'=>explode('.',$fileName)[0].'.zip',
                    'link'=>explode('?',$result['oss-request-url'])[0],
                    'status'=>1,
                ];
                OrderExportRepo::getInstance()->saveData($log,$this->id);
            }
            return $result;
        }catch (\Exception $e){
            Log::error('商户订单导出异常'.$e->getMessage());
            return;
        }
    }

    /**
     * 获取表名称
     * @param string $time
     * @param bool $as
     * @param string $prefix
     * @return string
     */
    protected  function getTableName(string $time, bool $as = true, string $prefix = ""): string
    {
        $tableName = $prefix . "orders_" . date("Ym", strtotime($time));
        if (!$as) {
            return $tableName;
        }
        return $tableName . " as " . $prefix . self::TmpTableName;
    }

    /**
     * 设置查询条件
     * @param $query
     * @param array $where
     * @param string $table
     * @return void
     */
    protected function setWhere($query, array $where, string $table = ""): void
    {
        if (!empty($table)) {
            $table .= ".";
        }
        if (isset($where["user_id"]) && $where["user_id"] != "") {
            if (is_array($where["user_id"])) {
                $query->whereIn($table . "user_id", $where["user_id"]);
            } else {
                $query->where($table . "user_id", $where["user_id"]);
            }
        }

        if (!empty($where["store_ids"])) {
            $query->whereIn($table . "store_id", $where["store_ids"]);
        }

        if (!empty($where["store_id"])) {
            if (is_array($where["store_id"])) {
                $query->whereIn($table . "store_id", $where["store_id"]);
            } else {
                $query->where($table . "store_id", $where["store_id"]);
            }
        }

        if (!empty($where["pay_status"])) {
            $query->where($table . "pay_status", $where["pay_status"]);
        }

        if (!empty($where["company"])) {
            $query->where($table . "company", $where["company"]);
        }

        if (!empty($where["start_date"])) {
            $query->where($table . "created_at", ">=", $where["start_date"]);
        }

        if (!empty($where["end_date"])) {
            $query->where($table . "created_at", "<=", $where["end_date"]);
        }

        if (!empty($where["total_amount"])) {
            $query->where($table . "total_amount", $where["total_amount"]);
        }
        if (!empty($where["device_id"])) {
            $query->where($table . "device_id", $where["device_id"]);
        }
        if (!empty($where["out_trade_no"])) {
            $query->where($table . "out_trade_no", $where["out_trade_no"]);
        }

        if (!empty($where["trade_no"])) {
            $query->where($table . "trade_no", $where["trade_no"]);
        }

        if (!empty($where["ways_source"])) {
            $query->where($table . "ways_source", $where["ways_source"]);
        }

        if (!empty($where["qwx_no"])) {
            $query->where($table . "qwx_no", $where["qwx_no"]);
        }

        if (!empty($where["auth_code"])) {
            $query->where($table . "auth_code", $where["auth_code"]);
        }
    }

    /**
     * 文件缓冲
     * @param string $filePath
     * @param string $fileName
     * @param $title
     * @param \Generator $orders
     * @param callable $callable
     * @return bool
     */
    protected function flushFile(string $filePath, string $fileName, $title, \Generator $orders, callable $callable)
    {
        try {
            if (!is_dir($filePath)) {
                mkdir($filePath, 0777, true);
            }
            $file = $filePath . $fileName;
            $fileObj = fopen($file, 'w');
            fwrite($fileObj, "\xEF\xBB\xBF"); // UTF-8 BOM
            fputcsv($fileObj, $title);
            $counter = 0;
            foreach ($orders as $order) {
                fputcsv($fileObj, $callable($order));
                // 每5000行刷新一次文件缓冲
                if (++$counter % 5000 === 0) {
                    fflush($fileObj);
                    flush();
                    gc_collect_cycles();
//                    // 可选：记录进度
//                    Log::info("商户订单导出文件到 {$counter} 行刷新文件缓冲");
                }
                unset($order);
            }
            fclose($fileObj);

            return true;
        }catch (\Exception $e){
            Log::error('商户订单导出写入文件失败'.$e->getMessage());
            return false;
        }
    }
}